package com.yitianyike.lockscreen.ces.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.yitianyike.lockscreen.ces.dao.PackageDAO;
import com.yitianyike.lockscreen.ces.model.PackageInfo;
import com.yitianyike.lockscreen.ces.model.response.Recommend;

@Component("packageDAO")
public class PackageDAOImpl extends BaseDAO implements PackageDAO {

	private String packageParam = "p.id,p.channel_code,p.package_id,p.package_name,p.pid,p.pid_path,"
			+ "p.image_id,p.package_style,p.status,p.package_order_no,p.use_range,p.start_time,p.end_time ,"
			+ "p.create_name,p.create_time,p.update_name,p.update_time ";

	@Override
	public List<PackageInfo> getPackage(int pid, String channelCode) {
		// TODO Auto-generated method stub
		Map<String, Object> paramMap = new HashMap<String, Object>();

		String selectSql = "select " + packageParam
				+ ", i.icon_url as icon from cms_package p, cms_image i, cms_channel c  where p.pid=:pid "
				+"  AND c.`status`=1 AND p.channel_code=:channelCode"
				+ " AND p.channel_code=c.channel_code AND p.image_id=i.id  AND p.status=1 AND p.use_range=1";

		paramMap.put("pid", pid);
		paramMap.put("channelCode", channelCode);
		List<PackageInfo> list = new ArrayList<PackageInfo>();
		try {
			list = this.getNamedParameterJdbcTemplate().query(selectSql, paramMap, new RowMapper<PackageInfo>() {
				@Override
				public PackageInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					PackageInfo mi = new PackageInfo();
					mi.setChannelCode(rs.getString("channel_code"));
					mi.setEndTime(rs.getLong("end_time"));
					mi.setStartTime(rs.getLong("start_time"));
					mi.setImageId(rs.getInt("image_id"));
					mi.setIcon(rs.getString("icon"));
					mi.setPackageStyle(rs.getInt("package_style"));
					mi.setPackageId(rs.getInt("package_id"));
					mi.setPackageName(rs.getString("package_name"));
					mi.setPackageOrderNo(rs.getInt("package_order_no"));
					mi.setPid(rs.getInt("pid"));
					mi.setId(rs.getInt("id"));
					return mi;
				}

			});
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return list;

	}

	@Override
	public List<Map<String, Object>> organizeMiddle(Map<String, String> parmMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT p.package_name,p.package_order_no,i.icon_url,p.package_id FROM cms_package p,cms_channel c,cms_image i WHERE p.channel_code=c.channel_code AND i.id=p.image_id AND p.channel_code=:channel_code   AND p.status=1  AND p.use_range=3 ORDER BY p.package_order_no");

		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), parmMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> recommendMap = new HashMap<String, Object>();
						String package_name = rs.getString("package_name");
						int package_order_no = rs.getInt("package_order_no");
						String icon_url = rs.getString("icon_url");
						int package_id = rs.getInt("package_id");
						recommendMap.put("icon", icon_url);
						recommendMap.put("title", package_name);
						recommendMap.put("order_no", package_order_no);
						recommendMap.put("pid", package_id);
						return recommendMap;
					}
				});

		return resultList;
	}

	@Override
	public List<Recommend> organizeTail(Map<String, String> parmMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT p.package_name,p.package_id,p.package_order_no FROM  cms_package p,cms_channel c WHERE  p.channel_code=c.channel_code  AND p.channel_code=:channel_code  AND p.use_range=4 ORDER BY p.package_order_no");

		List<Recommend> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), parmMap,
				new RowMapper<Recommend>() {
					@Override
					public Recommend mapRow(ResultSet rs, int rowNum) throws SQLException {
						Recommend recommend = new Recommend();
						String package_name = rs.getString("package_name");
						int package_order_no = rs.getInt("package_order_no");
						int package_id = rs.getInt("package_id");
						recommend.setPackage_id(package_id);
						recommend.setPackage_name(package_name);
						recommend.setPackage_order_no(package_order_no);
						return recommend;
					}
				});

		return resultList;
	}

	@Override
	public List<Map<String, Object>> organizeTop(Map<String, String> parmMap) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				"SELECT r.`name`,r.descinfo,r.banner_url,r.h5_url FROM cms_recommend_extend r,cms_channel c WHERE r.channel_code=c.channel_code AND r.channel_code=:channel_code  AND r.`status`=1 ORDER BY order_no");
		List<Map<String, Object>> resultList = this.getNamedParameterJdbcTemplate().query(sql.toString(), parmMap,
				new RowMapper<Map<String, Object>>() {
					@Override
					public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
						Map<String, Object> recommendMap = new HashMap<String, Object>();
						String name = rs.getString("name");
						String descinfo = rs.getString("descinfo");
						String banner_url = rs.getString("banner_url");
						String h5_url = rs.getString("h5_url");
						recommendMap.put("title", name);
						recommendMap.put("desc_info", descinfo);
						recommendMap.put("h5_url", h5_url);
						recommendMap.put("banner_url", banner_url);
						return recommendMap;
					}
				});

		return resultList;
	}

	@Override
	public List<Integer> getRecommendVersion(Map<String, String> paramMap) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT recommend_version FROM cms_channel WHERE channel_code=:channel_code AND version=:version");

		List<Integer> recommendVersion = this.getNamedParameterJdbcTemplate().query(sql.toString(), paramMap,
				new RowMapper<Integer>() {
					@Override
					public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
						int recommend_version = rs.getInt("recommend_version");
						return recommend_version;
					}
				});

		return recommendVersion;
	}

	@Override
	public List<PackageInfo> getPackage(Integer i, Map<String, String> paramMap) {
		String selectSql = "select " + packageParam
				+ ", i.icon_url as icon from cms_package p, cms_image i, cms_channel c  where p.pid=:pid "
				+ " AND c.`status`=1  AND p.channel_code=:channel_code"
				+ " AND p.channel_code=c.channel_code AND p.image_id=i.id  AND p.status=1 AND p.use_range=6";
		paramMap.put("pid", i.toString());
		List<PackageInfo> list = new ArrayList<PackageInfo>();
		try {
			list = this.getNamedParameterJdbcTemplate().query(selectSql, paramMap, new RowMapper<PackageInfo>() {
				@Override
				public PackageInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					PackageInfo mi = new PackageInfo();
					mi.setChannelCode(rs.getString("channel_code"));
					mi.setEndTime(rs.getLong("end_time"));
					mi.setStartTime(rs.getLong("start_time"));
					mi.setImageId(rs.getInt("image_id"));
					mi.setIcon(rs.getString("icon"));
					mi.setPackageStyle(rs.getInt("package_style"));
					mi.setPackageId(rs.getInt("package_id"));
					mi.setPackageName(rs.getString("package_name"));
					mi.setPackageOrderNo(rs.getInt("package_order_no"));
					mi.setPid(rs.getInt("pid"));
					mi.setId(rs.getInt("id"));
					return mi;
				}

			});
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;

	}

	@Override
	public List<Integer> getPackageId(Map<String, String> paramMap) {
		String selectSql = "select p.package_id from cms_package p, cms_channel c  where  c.`status`=1  AND p.channel_code=:channel_code AND p.channel_code=c.channel_code   AND p.status=1 AND p.use_range=5";
		List<Integer> list = new ArrayList<Integer>();
		try {
			list = this.getNamedParameterJdbcTemplate().query(selectSql, paramMap, new RowMapper<Integer>() {
				@Override
				public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {

					int package_id = rs.getInt("package_id");
					return package_id;
				}

			});
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;

	}
}
